<?php


namespace app\common\command\Db;


use think\console\Command;
use think\console\Input;
use think\console\Output;
use think\Db;

class Backup extends Command
{
    protected function configure()
    {
        $this->setName('db:backup');
    }

    /**
     * @param Input $input
     * @param Output $output
     * @return int|void|null
     * @throws \think\Exception
     */
    protected function execute(Input $input, Output $output)
    {
        if (!is_dir(MYSQL_BACKUP_PATH)) {
            mkdir(MYSQL_BACKUP_PATH, 0777, true);
        }

        $hostname = config('database.hostname');
        $database = config('database.database');
        $username = config('database.username');
        $password = config('database.password');
        $filename = MYSQL_BACKUP_PATH . config('database.database') . date('_Y_m_d_H_i_s') . mt_rand(1000, 9999) . '.bak.sql';

        //使用mysqldump备份
        // $this->mysqldump($hostname, $database, $username, $password, $filename);

        //使用原生php备份
        $this->phpbackup($hostname, $database, $username, $password, $filename);
    }

    /**
     * @param $hostname
     * @param $database
     * @param $username
     * @param $password
     * @param $filename
     */
    protected function mysqldump($hostname, $database, $username, $password, $filename)
    {
        $command = sprintf('mysqldump -h%s --databases %s -u%s -p%s > %s'
            , $hostname
            , $database
            , $username
            , $password
            , $filename
        );

        exec($command, $output, $status);
    }

    /**
     * @param $hostname
     * @param $database
     * @param $username
     * @param $password
     * @param $filename
     * @throws \think\Exception
     */
    protected function phpbackup($hostname, $database, $username, $password, $filename)
    {
        //sql语句
        $sqls = [];

        //获取所有表名
        $tables = Db::query('show tables');

        foreach ($tables as $table) {
            $table_name = array_shift($table);
            $create_table = Db::query('show create table ' . $table_name);
            $sqls[] = sprintf('DROP TABLE IF EXISTS `%s`', $table_name);
            $sqls[] = array_shift($create_table)['create table'];
            $sqls[] = $this->getInsertTableSql($table_name);
        }
       
        file_put_contents($filename, implode(';' . PHP_EOL . PHP_EOL, array_filter($sqls)));
    }

    /**
     * @param $table_name
     * @return string
     * @throws \think\Exception
     */
    protected function getInsertTableSql($table_name)
    {
        $sql = '';

         $total = Db::table($table_name)->count();

        if ($total > 0 && $total < 10000) {
            $dataset = Db::table($table_name)->select();
            $sql = Db::table($table_name)->getBuilder()->insertAll($dataset, ['field' => '*', 'table' => $table_name, 'comment' => '']);
        }

        return $sql;
    }
}